package com.ruoyi.web.cp.service.ob;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.web.core.config.ConfigLoader;
import com.ruoyi.web.cp.domain.rm.ObGame;
import com.ruoyi.web.cp.service.WinxinService;
import com.ruoyi.web.mapper.ObGameMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.Serializable;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@Service
public class ObSxAllGoalService {
    @Autowired
    private ObGameMapper obGameMapper;
    @Autowired
    private ConfigLoader configLoader;
    @Autowired
    private WinxinService winxinService;

    public void execute(int days, double plv, String key) {
        DateTime date = DateUtil.offsetDay(new Date(), -1 * days);
        List<ObGame> obGames = obGameMapper.selectByDateRange3(date);
        String name = StrUtil.format("OB上下半场均进球-已比.xlsx");
        String path = configLoader.getRmPath() + "excel" + File.separator + name;
        String path2 = configLoader.getRmPath() + "excel" + File.separator + "OB上下半场均进球-未比.xlsx";
        File tempFile = new File(path);
        // 如果文件已存在，先删除
        if (tempFile.exists()) {
            FileUtil.del(tempFile);
        }
        File tempFile2 = new File(path2);
        // 如果文件已存在，先删除
        if (tempFile2.exists()) {
            FileUtil.del(tempFile2);
        }
        // 导出到 Excel
        exportObAxAllGoalToExcel2(obGames, plv, tempFile, tempFile2);
        winxinService.sendQlMedia(key, path);
        winxinService.sendQlMedia(key, path2);
    }

    public void exportObAxAllGoalToExcel(List<ObGame> ObGameList, double plv, File tempFile, File tempFile2) {
        // 创建 ExcelWriter 对象
        ExcelWriter writer = ExcelUtil.getWriter(tempFile);
        ExcelWriter writer2 = ExcelUtil.getWriter(tempFile2);

        // 手动设置表头
        String[] headers = {"日期", "联赛", "主队", "客队",
                "亚盘",
                "上下半场均进球-主", "红黑",
                "上下半场均进球-客", "红黑",
                "半场结果", "全场结果"};
        writer.writeHeadRow(Arrays.asList(headers));
        writer2.writeHeadRow(Arrays.asList(headers));

        int dataRowCount1 = 0, dataRowCount2 = 0;

        // 写入数据（假设 RmobDoubleMerge 有对应的 getter 方法）
        for (ObGame item : ObGameList) {
            String halfScore = item.getHalfScore();
            String allScore = item.getAllScore();
            Date date = item.getDate();
            String dateStr = DateUtil.format(date, "yyyy-MM-dd HH:mm");

            JSONObject details;
            if (item.getDetails() == null) {
                details = new JSONObject();
            } else {
                details = JSONObject.parseObject(item.getDetails());
            }
            Boolean sx_have_goal_plv_teamA_boo = null,
                    sx_have_goal_plv_teamB_boo = null;

            Double sx_have_goal_plv_teamA = details.getDouble("sx_have_goal_plv_teamA"),
                    sx_have_goal_plv_teamB = details.getDouble("sx_have_goal_plv_teamB");
            if (sx_have_goal_plv_teamA != null && sx_have_goal_plv_teamA > plv) {
                sx_have_goal_plv_teamA = null;
            }
            if (sx_have_goal_plv_teamB != null && sx_have_goal_plv_teamB > plv) {
                sx_have_goal_plv_teamB = null;
            }
            if (halfScore != null && allScore != null) {
                int[] halfScores = Arrays.stream(halfScore.split(":")).mapToInt(Integer::parseInt).toArray();
                int[] allScores = Arrays.stream(allScore.split(":")).mapToInt(Integer::parseInt).toArray();
                //上下半场均进球-主
                if (sx_have_goal_plv_teamA != null) {
                    sx_have_goal_plv_teamA_boo = halfScores[0] >= 1
                            && allScores[0] > halfScores[0];
                }
                //上下半场均进球-客
                if (sx_have_goal_plv_teamB != null) {
                    sx_have_goal_plv_teamB_boo = halfScores[1] >= 1
                            && allScores[1] > halfScores[1];
                }
            }
            String resultA = sx_have_goal_plv_teamA_boo == null ? null : (sx_have_goal_plv_teamA_boo ? "黑" : "红");
            String resultB = sx_have_goal_plv_teamB_boo == null ? null : (sx_have_goal_plv_teamB_boo ? "黑" : "红");

            List<? extends Serializable> row = Arrays.asList(
                    dateStr,            // 日期
                    item.getLeague(),          // 联赛
                    item.getTeamA(),           // 主队
                    item.getTeamB(),           // 客队
                    details.getString("asian_desc"),           // 亚盘
                    sx_have_goal_plv_teamA,  // 上下半场均进球-主
                    resultA,        // 红黑
                    sx_have_goal_plv_teamB,  // 上下半场均进球-客
                    resultB,        // 红黑
                    halfScore,          // 半场结果
                    allScore            // 全场结果
            );
            if ((allScore != null || item.getDate().getTime() < System.currentTimeMillis())
                    && (sx_have_goal_plv_teamA != null || sx_have_goal_plv_teamB != null)) {
                writer.writeRow(row);
                dataRowCount1++;
            }
            if (item.getDate().getTime() > System.currentTimeMillis()
                    && (sx_have_goal_plv_teamA != null || sx_have_goal_plv_teamB != null)) {
                writer2.writeRow(row);
                dataRowCount2++;
            }
        }

        // 替换原有的公式处理代码块为以下两行
        writeFormulas(writer, dataRowCount1);
        writeFormulas(writer2, dataRowCount2);

        int[] columnWidths = {
                18 * 256, // 日期
                20 * 256, // 联赛
                22 * 256, // 主队
                22 * 256, // 客队
                8 * 256,  // 亚盘
                18 * 256, // 上下半场均进球-主
                8 * 256, // 红黑
                18 * 256, // 上下半场均进球-客
                8 * 256, // 红黑
                8 * 256,  // 半场结果
                8 * 256,  // 全场结果
        };

        for (int i = 0; i < columnWidths.length; i++) {
            writer.getSheet().setColumnWidth(i, columnWidths[i]);
        }
        for (int i = 0; i < columnWidths.length; i++) {
            writer2.getSheet().setColumnWidth(i, columnWidths[i]);
        }
        // 关闭流
        writer.close();
        writer2.close();
    }

    // 提取为公共方法
    private void writeFormulas(ExcelWriter writer, int dataRowCount) {
        if (dataRowCount > 0) {
            // F列是"上下半场均进球-主"列，索引为5
            // G列是"红黑"列，索引为6
            // H列是"上下半场均进球-客"列，索引为7
            // I列是"红黑"列，索引为8
            // 数据从第2行开始（索引1），结束于 dataRowCount + 1
            int endRow = dataRowCount + 1; // 表头(1) + 数据行数

            // 为F列和H列创建SUM-COUNT公式（只计算数值单元格）
            String formulaF = StrUtil.format("SUM(F2:F{})-COUNT(F2:F{})-SUMIF(G2:G{},\"黑\",F2:F{})+COUNTIF(G2:G{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);
            String formulaH = StrUtil.format("SUM(H2:F{})-COUNT(H2:F{})-SUMIF(G2:G{},\"黑\",H2:F{})+COUNTIF(G2:G{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);

            // 为G列和I列创建COUNT公式（统计红黑数量）
            String formulaG = String.format("COUNTIF(G2:G%d, \"黑\")", endRow);
            String formulaI = String.format("COUNTIF(I2:I%d, \"黑\")", endRow);

            // 在writer中添加公式行
            List<Object> formulaRow = Arrays.asList(
                    "合计:",
                    "",
                    "",
                    "",
                    "",
                    "",  // F列公式占位
                    "",  // G列公式占位
                    "",  // H列公式占位
                    "",  // I列公式占位
                    "",
                    ""
            );

            writer.writeRow(formulaRow);

            // 设置公式单元格
            int formulaRowIndex = endRow + 1; // 公式行的索引（1-based）

            // 处理Excel文件的公式
            org.apache.poi.ss.usermodel.Row row = writer.getSheet().getRow(formulaRowIndex - 1);
            if (row == null) {
                row = writer.getSheet().createRow(formulaRowIndex - 1);
            }

            // F列公式 (上下半场均进球-主)
            org.apache.poi.ss.usermodel.Cell cellF = row.getCell(5);
            if (cellF == null) {
                cellF = row.createCell(5);
            }
            cellF.setCellFormula(formulaF);

            // G列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellG = row.getCell(6);
            if (cellG == null) {
                cellG = row.createCell(6);
            }
            cellG.setCellFormula(formulaG);

            // H列公式 (上下半场均进球-客)
            org.apache.poi.ss.usermodel.Cell cellH = row.getCell(7);
            if (cellH == null) {
                cellH = row.createCell(7);
            }
            cellH.setCellFormula(formulaH);

            // I列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellI = row.getCell(8);
            if (cellI == null) {
                cellI = row.createCell(8);
            }
            cellI.setCellFormula(formulaI);
        }
    }

    public void exportObAxAllGoalToExcel2(List<ObGame> ObGameList, double plv, File tempFile, File tempFile2) {
        // 创建 ExcelWriter 对象
        ExcelWriter writer = ExcelUtil.getWriter(tempFile);
        ExcelWriter writer2 = ExcelUtil.getWriter(tempFile2);

        // 手动设置表头
        String[] headers = {"日期", "联赛", "主队", "客队",
                "亚盘",
                "主-是", "红黑",
                "主-否", "红黑",
                "客-是", "红黑",
                "客-否", "红黑",
                "半场结果", "全场结果"};
        writer.writeHeadRow(Arrays.asList(headers));
        writer2.writeHeadRow(Arrays.asList(headers));

        int dataRowCount1 = 0, dataRowCount2 = 0;

        // 写入数据（假设 RmobDoubleMerge 有对应的 getter 方法）
        for (ObGame item : ObGameList) {
            String halfScore = item.getHalfScore();
            String allScore = item.getAllScore();
            Date date = item.getDate();
            String dateStr = DateUtil.format(date, "yyyy-MM-dd HH:mm");

            JSONObject details;
            if (item.getDetails() == null) {
                details = new JSONObject();
            } else {
                details = JSONObject.parseObject(item.getDetails());
            }
            Boolean sxHaveGoalPlvTABoo = null;
            Boolean sxHaveGoalPlvFABoo = null;
            Boolean sxHaveGoalPlvTBBoo = null;
            Boolean sxHaveGoalPlvFBBoo = null;

            Double sxHaveGoalPlvTA = details.getDouble("sx_have_goal_plv_t_teamA");
            Double sxHaveGoalPlvTB = details.getDouble("sx_have_goal_plv_t_teamB");
            Double sxHaveGoalPlvFA = details.getDouble("sx_have_goal_plv_teamA");
            Double sxHaveGoalPlvFB = details.getDouble("sx_have_goal_plv_teamB");
            if (sxHaveGoalPlvFA != null && sxHaveGoalPlvFA > plv) {
                sxHaveGoalPlvTA = null;
                sxHaveGoalPlvFA = null;
            }
            if (sxHaveGoalPlvFB != null && sxHaveGoalPlvFB > plv) {
                sxHaveGoalPlvTB = null;
                sxHaveGoalPlvFB = null;
            }
            if (halfScore != null && allScore != null) {
                int[] halfScores = Arrays.stream(halfScore.split(":")).mapToInt(Integer::parseInt).toArray();
                int[] allScores = Arrays.stream(allScore.split(":")).mapToInt(Integer::parseInt).toArray();
                //上下半场均进球-主-是
                if (sxHaveGoalPlvTA != null) {
                    sxHaveGoalPlvTABoo = halfScores[0] >= 1
                            && allScores[0] > halfScores[0];
                }
                if (sxHaveGoalPlvFA != null) {
                    sxHaveGoalPlvFABoo = halfScores[0] == 0
                            || allScores[0] == halfScores[0];
                }
                //上下半场均进球-客-是
                if (sxHaveGoalPlvTB != null) {
                    sxHaveGoalPlvTBBoo = halfScores[1] >= 1
                            && allScores[1] > halfScores[1];
                }
                if (sxHaveGoalPlvFB != null) {
                    sxHaveGoalPlvFBBoo = halfScores[1] == 0
                            || allScores[1] == halfScores[1];
                }
            }
            String resultTA = sxHaveGoalPlvTABoo == null ? null : (sxHaveGoalPlvTABoo ? "红" : "黑");
            String resultFA = sxHaveGoalPlvFABoo == null ? null : (sxHaveGoalPlvFABoo ? "红" : "黑");
            String resultTB = sxHaveGoalPlvTBBoo == null ? null : (sxHaveGoalPlvTBBoo ? "红" : "黑");
            String resultFB = sxHaveGoalPlvFBBoo == null ? null : (sxHaveGoalPlvFBBoo ? "红" : "黑");

            List<? extends Serializable> row = Arrays.asList(
                    dateStr,            // 日期
                    item.getLeague(),          // 联赛
                    item.getTeamA(),           // 主队
                    item.getTeamB(),           // 客队
                    details.getString("asian_desc"),           // 亚盘
                    sxHaveGoalPlvTA,  // 上下半场均进球-主-是
                    resultTA,        // 红黑
                    sxHaveGoalPlvFA,  // 上下半场均进球-主-否
                    resultFA,        // 红黑
                    sxHaveGoalPlvTB,  // 上下半场均进球-客-是
                    resultTB,        // 红黑
                    sxHaveGoalPlvFB,  // 上下半场均进球-客-否
                    resultFB,        // 红黑
                    halfScore,          // 半场结果
                    allScore            // 全场结果
            );
            if ((allScore != null || item.getDate().getTime() > (System.currentTimeMillis() - 1000 * 60 * 60 * 3))
                    && (sxHaveGoalPlvTA != null || sxHaveGoalPlvTB != null || sxHaveGoalPlvFA != null || sxHaveGoalPlvFB != null)) {
                writer.writeRow(row);
                dataRowCount1++;
            }
            if (item.getDate().getTime() > System.currentTimeMillis()
                    && (sxHaveGoalPlvTA != null || sxHaveGoalPlvTB != null)) {
                writer2.writeRow(row);
                dataRowCount2++;
            }
        }

        // 替换原有的公式处理代码块为以下两行
        writeFormulas2(writer, dataRowCount1);
        writeFormulas2(writer2, dataRowCount2);

        int[] columnWidths = {
                18 * 256, // 日期
                20 * 256, // 联赛
                22 * 256, // 主队
                22 * 256, // 客队
                8 * 256,  // 亚盘
                18 * 256, // 上下半场均进球-主-是
                8 * 256, // 红黑
                18 * 256, // 上下半场均进球-主-否
                8 * 256, // 红黑
                18 * 256, // 上下半场均进球-客-是
                8 * 256, // 红黑
                18 * 256, // 上下半场均进球-客-否
                8 * 256, // 红黑
                8 * 256,  // 半场结果
                8 * 256,  // 全场结果
        };

        for (int i = 0; i < columnWidths.length; i++) {
            writer.getSheet().setColumnWidth(i, columnWidths[i]);
        }
        for (int i = 0; i < columnWidths.length; i++) {
            writer2.getSheet().setColumnWidth(i, columnWidths[i]);
        }
        // 关闭流
        writer.close();
        writer2.close();
    }

    // 提取为公共方法
    private void writeFormulas2(ExcelWriter writer, int dataRowCount) {
        if (dataRowCount > 0) {
            // F列是"上下半场均进球-主"列，索引为5
            // G列是"红黑"列，索引为6
            // H列是"上下半场均进球-客"列，索引为7
            // I列是"红黑"列，索引为8
            // 数据从第2行开始（索引1），结束于 dataRowCount + 1
            int endRow = dataRowCount + 1; // 表头(1) + 数据行数

            // 为F列和H列创建SUM-COUNT公式（只计算数值单元格）
            String formulaF = StrUtil.format("SUM(F2:F{})-COUNT(F2:F{})-SUMIF(G2:G{},\"黑\",F2:F{})+COUNTIF(G2:G{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);
            String formulaH = StrUtil.format("SUM(H2:H{})-COUNT(H2:H{})-SUMIF(H2:H{},\"黑\",H2:H{})+COUNTIF(I2:I{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);
            String formulaJ = StrUtil.format("SUM(J2:J{})-COUNT(J2:J{})-SUMIF(J2:J{},\"黑\",J2:J{})+COUNTIF(K2:K{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);
            String formulaL = StrUtil.format("SUM(L2:L{})-COUNT(L2:L{})-SUMIF(L2:L{},\"黑\",L2:L{})+COUNTIF(M2:M{},\"黑\")",
                    endRow, endRow, endRow, endRow, endRow);

            // 为G列和I列创建COUNT公式（统计红黑数量）
            String formulaG = String.format("COUNTIF(G2:G%d, \"黑\")", endRow);
            String formulaI = String.format("COUNTIF(I2:I%d, \"黑\")", endRow);
            String formulaK = String.format("COUNTIF(K2:K%d, \"黑\")", endRow);
            String formulaM = String.format("COUNTIF(M2:M%d, \"黑\")", endRow);

            // 在writer中添加公式行
            List<Object> formulaRow = Arrays.asList(
                    "合计:",
                    "",
                    "",
                    "",
                    "",
                    "",  // F列公式占位
                    "",  // G列公式占位
                    "",  // H列公式占位
                    "",  // I列公式占位
                    "",  // J列公式占位
                    "",  // K列公式占位
                    "",  // L列公式占位
                    "",  // M列公式占位
                    "",
                    ""
            );

            writer.writeRow(formulaRow);

            // 设置公式单元格
            int formulaRowIndex = endRow + 1; // 公式行的索引（1-based）

            // 处理Excel文件的公式
            org.apache.poi.ss.usermodel.Row row = writer.getSheet().getRow(formulaRowIndex - 1);
            if (row == null) {
                row = writer.getSheet().createRow(formulaRowIndex - 1);
            }

            // F列公式 (上下半场均进球-主)
            org.apache.poi.ss.usermodel.Cell cellF = row.getCell(5);
            if (cellF == null) {
                cellF = row.createCell(5);
            }
            cellF.setCellFormula(formulaF);

            // G列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellG = row.getCell(6);
            if (cellG == null) {
                cellG = row.createCell(6);
            }
            cellG.setCellFormula(formulaG);

            // H列公式 (上下半场均进球-主)
            org.apache.poi.ss.usermodel.Cell cellH = row.getCell(7);
            if (cellH == null) {
                cellH = row.createCell(7);
            }
            cellH.setCellFormula(formulaH);

            // I列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellI = row.getCell(8);
            if (cellI == null) {
                cellI = row.createCell(8);
            }
            cellI.setCellFormula(formulaI);

            // J列公式 (上下半场均进球-主)
            org.apache.poi.ss.usermodel.Cell cellJ = row.getCell(9);
            if (cellJ == null) {
                cellJ = row.createCell(9);
            }
            cellJ.setCellFormula(formulaJ);

            // K列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellK = row.getCell(10);
            if (cellK == null) {
                cellK = row.createCell(10);
            }
            cellK.setCellFormula(formulaK);

            // L列公式 (上下半场均进球-主)
            org.apache.poi.ss.usermodel.Cell cellL = row.getCell(11);
            if (cellL == null) {
                cellL = row.createCell(11);
            }
            cellL.setCellFormula(formulaL);

            // M列公式 (红黑统计)
            org.apache.poi.ss.usermodel.Cell cellM = row.getCell(12);
            if (cellM == null) {
                cellM = row.createCell(12);
            }
            cellM.setCellFormula(formulaM);
        }
    }
}
